package com.liy.framework.common.office.html;

import org.apache.poi.ss.util.*;
import java.text.*;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import java.io.*;

public class Excel2Html
{
    static String[] bordesr;
    static String[] borderStyles;
    
    public static String readExcelToHtml(final String filePath, final String htmlPositon, final boolean isWithStyle) {
        InputStream is = null;
        String htmlExcel = null;
        try {
            final File sourcefile = new File(filePath);
            is = new FileInputStream(sourcefile);
            final Workbook wb = WorkbookFactory.create(is);
            if (wb instanceof XSSFWorkbook) {
                final XSSFWorkbook xWb = (XSSFWorkbook)wb;
                htmlExcel = getExcelInfo((Workbook)xWb, isWithStyle);
            }
            else if (wb instanceof HSSFWorkbook) {
                final HSSFWorkbook hWb = (HSSFWorkbook)wb;
                htmlExcel = getExcelInfo((Workbook)hWb, isWithStyle);
            }
            writeFile(htmlExcel, htmlPositon);
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                is.close();
            }
            catch (IOException e2) {
                e2.printStackTrace();
            }
        }
        finally {
            try {
                is.close();
            }
            catch (IOException e3) {
                e3.printStackTrace();
            }
        }
        return htmlPositon;
    }
    
    private static String getExcelInfo(final Workbook wb, final boolean isWithStyle) {
        final StringBuffer sb = new StringBuffer();
        final Sheet sheet = wb.getSheetAt(0);
        final int lastRowNum = sheet.getLastRowNum();
        final Map<String, String>[] map = getRowSpanColSpanMap(sheet);
        sb.append("<table style='border-collapse:collapse;' width='100%'>");
        Row row = null;
        Cell cell = null;
        for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; ++rowNum) {
            row = sheet.getRow(rowNum);
            if (row == null) {
                sb.append("<tr><td ><nobr> </nobr></td></tr>");
            }
            else {
                sb.append("<tr>");
                for (int lastColNum = row.getLastCellNum(), colNum = 0; colNum < lastColNum; ++colNum) {
                    cell = row.getCell(colNum);
                    if (cell == null) {
                        sb.append("<td> </td>");
                    }
                    else {
                        final String stringValue = getCellValue(cell);
                        if (map[0].containsKey(rowNum + "," + colNum)) {
                            final String pointString = map[0].get(rowNum + "," + colNum);
                            map[0].remove(rowNum + "," + colNum);
                            final int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
                            final int bottomeCol = Integer.valueOf(pointString.split(",")[1]);
                            final int rowSpan = bottomeRow - rowNum + 1;
                            final int colSpan = bottomeCol - colNum + 1;
                            sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' ");
                        }
                        else {
                            if (map[1].containsKey(rowNum + "," + colNum)) {
                                map[1].remove(rowNum + "," + colNum);
                                continue;
                            }
                            sb.append("<td ");
                        }
                        if (isWithStyle) {
                            dealExcelStyle(wb, sheet, cell, sb);
                        }
                        sb.append("><span>");
                        if (stringValue == null || "".equals(stringValue.trim())) {
                            sb.append("   ");
                        }
                        else {
                            sb.append(stringValue.replace(String.valueOf('?'), " "));
                        }
                        sb.append("</span></td>");
                    }
                }
                sb.append("</tr>");
            }
        }
        sb.append("</table>");
        return sb.toString();
    }
    
    private static Map<String, String>[] getRowSpanColSpanMap(final Sheet sheet) {
        final Map<String, String> map0 = new HashMap<String, String>();
        final Map<String, String> map2 = new HashMap<String, String>();
        final int mergedNum = sheet.getNumMergedRegions();
        CellRangeAddress range = null;
        for (int i = 0; i < mergedNum; ++i) {
            range = sheet.getMergedRegion(i);
            final int topRow = range.getFirstRow();
            final int topCol = range.getFirstColumn();
            final int bottomRow = range.getLastRow();
            final int bottomCol = range.getLastColumn();
            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
            for (int tempRow = topRow; tempRow <= bottomRow; ++tempRow) {
                for (int tempCol = topCol; tempCol <= bottomCol; ++tempCol) {
                    map2.put(tempRow + "," + tempCol, "");
                }
            }
            map2.remove(topRow + "," + topCol);
        }
        final Map[] map3 = { map0, map2 };
        return (Map<String, String>[])map3;
    }
    
    private static String getCellValue(final Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            case 0: {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    }
                    else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    final Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                    break;
                }
                if (cell.getCellStyle().getDataFormat() == 58) {
                    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    final double value = cell.getNumericCellValue();
                    final Date date2 = DateUtil.getJavaDate(value);
                    result = sdf.format(date2);
                    break;
                }
                final double value2 = cell.getNumericCellValue();
                final CellStyle style = cell.getCellStyle();
                final DecimalFormat format = new DecimalFormat();
                final String temp = style.getDataFormatString();
                if (temp.equals("General")) {
                    format.applyPattern("#");
                }
                result = format.format(value2);
                break;
            }
            case 1: {
                result = cell.getRichStringCellValue().toString();
                break;
            }
            case 3: {
                result = "";
                break;
            }
            default: {
                result = "";
                break;
            }
        }
        return result;
    }
    
    private static void dealExcelStyle(final Workbook wb, final Sheet sheet, final Cell cell, final StringBuffer sb) {
        final CellStyle cellStyle = cell.getCellStyle();
        if (cellStyle != null) {
            final short alignment = cellStyle.getAlignment();
            final short verticalAlignment = cellStyle.getVerticalAlignment();
            sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");
            if (wb instanceof XSSFWorkbook) {
                final XSSFFont xf = ((XSSFCellStyle)cellStyle).getFont();
                final short boldWeight = xf.getBoldweight();
                final String align = convertAlignToHtml(alignment);
                sb.append("style='");
                sb.append("font-weight:" + boldWeight + ";");
                sb.append("font-size: " + xf.getFontHeight() / 2 + "%;");
                final int columnWidth = (int)sheet.getColumnWidthInPixels(cell.getColumnIndex());
                sb.append("word-wrap:break-word;word-break:break-all;");
                sb.append("width:" + columnWidth + "px;");
                sb.append("text-align:" + align + ";");
                final XSSFColor xc = xf.getXSSFColor();
                if (xc != null && !"".equals(xc)) {
                    sb.append("color:#" + xc.getARGBHex().substring(2) + ";");
                }
                final XSSFColor bgColor = (XSSFColor)cellStyle.getFillForegroundColorColor();
                if (bgColor != null && !"".equals(bgColor)) {
                    sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";");
                }
                sb.append(getBorderStyle(0, cellStyle.getBorderTop(), ((XSSFCellStyle)cellStyle).getTopBorderXSSFColor()));
                sb.append(getBorderStyle(1, cellStyle.getBorderRight(), ((XSSFCellStyle)cellStyle).getRightBorderXSSFColor()));
                sb.append(getBorderStyle(2, cellStyle.getBorderBottom(), ((XSSFCellStyle)cellStyle).getBottomBorderXSSFColor()));
                sb.append(getBorderStyle(3, cellStyle.getBorderLeft(), ((XSSFCellStyle)cellStyle).getLeftBorderXSSFColor()));
            }
            else if (wb instanceof HSSFWorkbook) {
                final HSSFFont hf = ((HSSFCellStyle)cellStyle).getFont(wb);
                final short boldWeight = hf.getBoldweight();
                final short fontColor = hf.getColor();
                sb.append("style='");
                final HSSFPalette palette = ((HSSFWorkbook)wb).getCustomPalette();
                HSSFColor hc = palette.getColor(fontColor);
                sb.append("font-weight:" + boldWeight + ";");
                sb.append("font-size: " + hf.getFontHeight() / 2 + "%;");
                final String align2 = convertAlignToHtml(alignment);
                sb.append("text-align:" + align2 + ";");
                final String fontColorStr = convertToStardColor(hc);
                if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
                    sb.append("color:" + fontColorStr + ";");
                }
                final int columnWidth2 = (int)sheet.getColumnWidthInPixels(cell.getColumnIndex());
                sb.append("width:" + columnWidth2 + "px;");
                sb.append("word-wrap:break-word;word-break:break-all;");
                final short bgColor2 = cellStyle.getFillForegroundColor();
                hc = palette.getColor(bgColor2);
                final String bgColorStr = convertToStardColor(hc);
                if (bgColorStr != null && !"".equals(bgColorStr.trim())) {
                    sb.append("background-color:" + bgColorStr + ";");
                }
                sb.append(getBorderStyle(palette, 0, cellStyle.getBorderTop(), cellStyle.getTopBorderColor()));
                sb.append(getBorderStyle(palette, 1, cellStyle.getBorderRight(), cellStyle.getRightBorderColor()));
                sb.append(getBorderStyle(palette, 3, cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()));
                sb.append(getBorderStyle(palette, 2, cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()));
            }
            sb.append("' ");
        }
    }
    
    private static String convertAlignToHtml(final short alignment) {
        String align = "center";
        switch (alignment) {
            case 1: {
                align = "left";
                break;
            }
            case 2: {
                align = "center";
                break;
            }
            case 3: {
                align = "right";
                break;
            }
        }
        return align;
    }
    
    private static String convertVerticalAlignToHtml(final short verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment) {
            case 2: {
                valign = "bottom";
                break;
            }
            case 1: {
                valign = "center";
                break;
            }
            case 0: {
                valign = "top";
                break;
            }
        }
        return valign;
    }
    
    private static String convertToStardColor(final HSSFColor hc) {
        final StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            if (HSSFColor.AUTOMATIC.index == hc.getIndex()) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; ++i) {
                sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
            }
        }
        return sb.toString();
    }
    
    private static String fillWithZero(final String str) {
        if (str != null && str.length() < 2) {
            return "0" + str;
        }
        return str;
    }
    
    private static String getBorderStyle(final HSSFPalette palette, final int b, final short s, final short t) {
        if (s == 0) {
            return Excel2Html.bordesr[b] + Excel2Html.borderStyles[s] + "#d0d7e5 1px;";
        }
        String borderColorStr = convertToStardColor(palette.getColor(t));
        borderColorStr = ((borderColorStr == null || borderColorStr.length() < 1) ? "#000000" : borderColorStr);
        return Excel2Html.bordesr[b] + Excel2Html.borderStyles[s] + borderColorStr + " 1px;";
    }
    
    private static String getBorderStyle(final int b, final short s, final XSSFColor xc) {
        if (s == 0) {
            return Excel2Html.bordesr[b] + Excel2Html.borderStyles[s] + "#d0d7e5 1px;";
        }
        if (xc != null && !"".equals(xc)) {
            String borderColorStr = xc.getARGBHex();
            borderColorStr = ((borderColorStr == null || borderColorStr.length() < 1) ? "#000000" : borderColorStr.substring(2));
            return Excel2Html.bordesr[b] + Excel2Html.borderStyles[s] + borderColorStr + " 1px;";
        }
        return "";
    }
    
    private static void writeFile(final String content, final String htmlPath) {
        final File file2 = new File(htmlPath);
        final StringBuilder sb = new StringBuilder();
        try {
            file2.createNewFile();
            sb.append("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Html Test</title></head><body>");
            sb.append("<div>");
            sb.append(content);
            sb.append("</div>");
            sb.append("</body></html>");
            final PrintStream printStream = new PrintStream(new FileOutputStream(file2));
            printStream.println(new String(sb.toString().getBytes("UTF-8"), "UTF-8"));
        }
        catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    static {
        Excel2Html.bordesr = new String[] { "border-top:", "border-right:", "border-bottom:", "border-left:" };
        Excel2Html.borderStyles = new String[] { "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid", "solid", "solid", "solid" };
    }
}
